
  with tmp_detail as (
    select
    id
     ,code
     ,waybill_no
     ,receive_network_id
     ,receive_network_name
     ,receive_network_code
     ,registration_network_id
     ,registration_network_name
     ,registration_network_code
     ,proble_type_id
     ,proble_type_name
     ,proble_type_subject_id
     ,proble_type_subject_name
     ,proble_type_subject_code
     ,proble_description
     ,paths
     ,proble_piece_status
     ,proble_piece_source
     ,registrant_phone
     ,registrant_telephone
     ,list_id
     ,package_number
     ,receive_network_parent_id
     ,receive_network_parent_name
     ,receive_network_parent_code
     ,registration_network_parent_id
     ,registration_network_parent_name
     ,registration_network_parent_code
     ,scan_pda
     ,scan_type_code
     ,scan_type_name
     ,scan_time
     ,scan_by_contact
     ,scan_network_contact
     ,scan_network_type_name
     ,scan_network_type_id
     ,scan_network_city
     ,scan_network_province
     ,create_by
     ,create_by_code
     ,update_by_code
     ,update_by
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,is_delete
     ,country_id
     ,is_enable
     ,customer_code
     ,customer_name
     ,sender_time
     ,sender_network_name
     ,second_level_type_code
     ,second_level_type_name
     ,create_date
     ,first_reply_time
     ,receiver_reply_time
     ,reply_duration
     ,need_reply
     ,videos
     ,last_reply_user_id
     ,last_reply_user_name
     ,last_reply_time
     ,finish_duration
     ,sender_network_id
     ,blank_out_status
     ,is_blank_out
     ,blank_out_no
     ,process_status
     ,know_network
      ,process_result
      ,send_no_arrive_time
      ,keep_warehouse
      ,waybill_type
      ,reg_network_mr_code
      ,reg_network_mr_name
      ,receive_network_mr_code
      ,receive_network_mr_name
      ,reg_network_proxy_code
      ,reg_network_proxy_name
      ,receive_network_proxy_code
      ,receive_network_proxy_name
      ,in_out_bound
      ,order_source_name
      ,order_source_code
     ,date_format(create_time,'yyyy-MM-dd') as dt
    from jms_ods.registration_problem_piece
   where dt = '{{ execution_date | cst_ds }}'
   union all
select
    id
     ,code
     ,waybill_no
     ,receive_network_id
     ,receive_network_name
     ,receive_network_code
     ,registration_network_id
     ,registration_network_name
     ,registration_network_code
     ,proble_type_id
     ,proble_type_name
     ,proble_type_subject_id
     ,proble_type_subject_name
     ,proble_type_subject_code
     ,proble_description
     ,paths
     ,proble_piece_status
     ,proble_piece_source
     ,registrant_phone
     ,registrant_telephone
     ,list_id
     ,package_number
     ,receive_network_parent_id
     ,receive_network_parent_name
     ,receive_network_parent_code
     ,registration_network_parent_id
     ,registration_network_parent_name
     ,registration_network_parent_code
     ,scan_pda
     ,scan_type_code
     ,scan_type_name
     ,scan_time
     ,scan_by_contact
     ,scan_network_contact
     ,scan_network_type_name
     ,scan_network_type_id
     ,scan_network_city
     ,scan_network_province
     ,create_by
     ,create_by_code
     ,update_by_code
     ,update_by
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,is_delete
     ,country_id
     ,is_enable
     ,customer_code
     ,customer_name
     ,sender_time
     ,sender_network_name
     ,second_level_type_code
     ,second_level_type_name
     ,create_date
     ,first_reply_time
     ,receiver_reply_time
     ,reply_duration
     ,need_reply
     ,videos
     ,last_reply_user_id
     ,last_reply_user_name
     ,last_reply_time
     ,finish_duration
     ,sender_network_id
     ,blank_out_status
     ,is_blank_out
     ,blank_out_no
     ,process_status
     ,know_network
     ,process_result
     ,send_no_arrive_time
     ,keep_warehouse
     ,waybill_type
     ,reg_network_mr_code
     ,reg_network_mr_name
     ,receive_network_mr_code
     ,receive_network_mr_name
     ,reg_network_proxy_code
     ,reg_network_proxy_name
     ,receive_network_proxy_code
     ,receive_network_proxy_name
     ,in_out_bound
     ,order_source_name
     ,order_source_code
     , dt
    from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
    where dt >= date_sub('{{ execution_date | cst_ds }}',29) and dt <='{{ execution_date | cst_ds }}'
),
     tmp_distinct_detail as
    (
    select * ,row_number() over(partition by id order by update_time desc) rn from tmp_detail
     )
  insert overwrite table jms_dwd.dwd_sqs_registration_problem_piece_base_dt partition (dt)
    select
        id
         ,code
         ,waybill_no
         ,receive_network_id
         ,receive_network_name
         ,receive_network_code
         ,registration_network_id
         ,registration_network_name
         ,registration_network_code
         ,proble_type_id
         ,proble_type_name
         ,proble_type_subject_id
         ,proble_type_subject_name
         ,proble_type_subject_code
         ,proble_description
         ,paths
         ,proble_piece_status
         ,proble_piece_source
         ,registrant_phone
         ,registrant_telephone
         ,list_id
         ,package_number
         ,receive_network_parent_id
         ,receive_network_parent_name
         ,receive_network_parent_code
         ,registration_network_parent_id
         ,registration_network_parent_name
         ,registration_network_parent_code
         ,scan_pda
         ,scan_type_code
         ,scan_type_name
         ,scan_time
         ,scan_by_contact
         ,scan_network_contact
         ,scan_network_type_name
         ,scan_network_type_id
         ,scan_network_city
         ,scan_network_province
         ,create_by
         ,create_by_code
         ,update_by_code
         ,update_by
         ,create_by_name
         ,update_by_name
         ,create_time
         ,update_time
         ,is_delete
         ,country_id
         ,is_enable
         ,customer_code
         ,customer_name
         ,sender_time
         ,sender_network_name
         ,second_level_type_code
         ,second_level_type_name
         ,create_date
         ,first_reply_time
         ,receiver_reply_time
         ,reply_duration
         ,need_reply
         ,videos
         ,last_reply_user_id
         ,last_reply_user_name
         ,last_reply_time
         ,finish_duration
         ,sender_network_id
         ,blank_out_status
         ,is_blank_out
         ,blank_out_no
         ,process_status
         ,know_network
         ,process_result
         ,send_no_arrive_time
         ,keep_warehouse
         ,waybill_type
         ,reg_network_mr_code
         ,reg_network_mr_name
         ,receive_network_mr_code
         ,receive_network_mr_name
         ,reg_network_proxy_code
         ,reg_network_proxy_name
         ,receive_network_proxy_code
         ,receive_network_proxy_name
         ,in_out_bound
         ,order_source_name
         ,order_source_code
         , dt
  from tmp_distinct_detail
  where rn=1
   and dt >= date_add('{{ execution_date | cst_ds }}', -29)
   and dt <= '{{ execution_date | cst_ds }}'
   distribute by dt, PMOD(HASH(RAND()), 5);